package com.gx.util.dbutil.jdbcdao;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by admin on 2016/11/1.
 * jdbc操作数据库
 */
public class JdbcDAO {
    private Logger log = LoggerFactory.getLogger(JdbcDAO.class);

    protected DataSource dataSource;

    public JdbcDAO buildDataSource(String propFile) {
        DataSource dataSource = null;
        try {
            dataSource = new DataSourceHepler().bulidProp(propFile).getDataSource();
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        this.dataSource = dataSource;
        return this;
    }

    /**
     * 获取连接
     * @return
     */
    protected Connection getConnection(){
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (Exception e) {
            log.error("jdbcDAO获取connection fail.",e);
        }
        return connection;
    }

    /**
     * 关闭
     * @param resultSet
     * @param statement
     * @param connection
     */
    protected void releaseDB(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error(e.getMessage(),e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error(e.getMessage(),e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error(e.getMessage(),e);
            }
        }
    }

//    public Map<String,Object> selectOne(String sql,Object...args){
//        Connection connection = null;
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        try{
//            connection = getConnection();
//            preparedStatement = connection.prepareStatement(sql);
//            for (int i = 0; i < args.length; i++) {
//                preparedStatement.setObject(i + 1, args[i]);
//            }
//            resultSet = preparedStatement.executeQuery();
//            //处理 ResultSet, 使用 while 循环
//            while (resultSet.next()) {
//                map = new HashMap<>();
//                for (String columnLabel : columnLabels) {
//                    Object value = resultSet.getObject(columnLabel);
//                    map.put(columnLabel, value);
//                }
//                // 把一条记录的一个 Map 对象放入List 中
//                values.add(map);
//            }
//
//            if(resultSet.next()) {
//                count = resultSet.getInt(1);
//            }else{
//                count = -1;
//            }
//        }catch (Exception e){
//            log.error(e.getMessage(),e);
//            count = -1;
//        } finally {
//            releaseDB(resultSet, preparedStatement, connection);
//        }
//        return  count;
//    }

    /**
     * 查询sql count
     * @param sql
     * @param args
     * @return -1：失败
     */
    public Integer selectForCount(String sql,Object...args){
        Integer count = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                count = resultSet.getInt(1);
            }else{
                count = -1;
            }
        }catch (Exception e){
            log.error(e.getMessage(),e);
            count = -1;
        } finally {
            releaseDB(resultSet, preparedStatement, connection);
        }
        return  count;
    }

    /**
     * 查询sql （结果集类型为Map<String,Object>）
     * @param sql
     * @param args
     * @return
     */
    public List<Map<String,Object>> selectForList(String sql,Object...args){
        List<Map<String, Object>> values = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            //处理结果集, 得到 Map 的 List, 其中一个 Map 对象就是一条记录. Map 的 key 为 reusltSet 中列的别名, Map 的 value为列的值.
            values = handleResultSetToMapList(resultSet);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        } finally {
            releaseDB(resultSet, preparedStatement, connection);
        }
        return values;
    }

    /**
     * 传入 SQL 语句和 Class 对象, 返回 SQL 语句查询到的记录对应的 Class 类的对象的集合
     * @param clazz: 对象的类型
     * @param sql: SQL 语句
     * @param args: 填充 SQL 语句的占位符的可变参数.
     * @return
     */
    public <T> List<T> selectForList(Class<T> clazz, String sql, Object... args) {
        List<T> list = new ArrayList<>();
        try {
            List<Map<String,Object>> values = selectForList(sql,args);
            list = transfterMapListToBeanList(clazz, values);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        return list;
    }


    /**
     * 执行sql语句
     * @param sql
     * @param args
     */
    public void execute(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            preparedStatement.execute();
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        } finally {
            releaseDB(null, preparedStatement, connection);
        }
    }


    /**
     * 转换List<Map> 为  List<T>
     * @param clazz
     * @param values
     * @return
     * @throws Exception
     */
    protected  <T> List<T> transfterMapListToBeanList(Class<T> clazz, List<Map<String, Object>> values) throws Exception {
        List<T> result = new ArrayList<>();
        T bean = null;
        if (values.size() > 0) {
            for (Map<String, Object> m : values) {
                //通过反射创建一个其他类的对象
                bean = clazz.newInstance();
                Field [] beanFields =clazz.getDeclaredFields();
                for(Field field: beanFields ){
                    //私有变量必须先设置Accessible为true
                    field.setAccessible(true);
                    for (Map.Entry<String, Object> entry : m.entrySet()) {
                        String propertyName = entry.getKey();
                        Object value = entry.getValue();
                        if (propertyName.equals(field.getName())){
                            field.set(bean, value);
                            break;
                        }
                    }
                }
                result.add(bean);
            }
        }
        return result;
    }


    /**
     * 处理结果集, 得到 Map 的一个 List, 其中一个 Map 对象对应一条记录
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private List<Map<String, Object>> handleResultSetToMapList(ResultSet resultSet) throws SQLException {
        // 键: 存放列的别名, 值: 存放列的值. 其中一个 Map 对象对应着一条记录
        List<Map<String, Object>> values = new ArrayList<>();
        List<String> columnLabels = getColumnLabels(resultSet);
        Map<String, Object> map = null;
        //处理 ResultSet, 使用 while 循环
        while (resultSet.next()) {
            map = new HashMap<>();
            for (String columnLabel : columnLabels) {
                Object value = resultSet.getObject(columnLabel);
                map.put(columnLabel, value);
            }
            // 把一条记录的一个 Map 对象放入List 中
            values.add(map);
        }
        return values;
    }

    /**
     * 获取结果集的 ColumnLabel 对应的 List
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<String> getColumnLabels(ResultSet rs) throws SQLException {
        List<String> labels = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            labels.add(rsmd.getColumnLabel(i + 1));
        }
        return labels;
    }

}
